package com.engine.modules.ytht.mapper;

import com.engine.init.Globle;
import com.engine.modules.ytht.entity.LocationEntity;
import com.engine.modules.ytht.entity.LocationResultEntity;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Slf4j
public class LocationMapper {

    @Autowired
    JdbcTemplate jdbcTemplate;

    public List<LocationEntity> SearchLocationFast(String name) {
        String name2 = "%" + name + "%";
        name2 = name2.replace("%%", "%");
        String sql = "SELECT id,pac,name,lvl ,st_x(geom) as lng,st_y(geom) as lat " +
                "FROM b_gis_point_location_all WHERE name like ?";
        List<LocationEntity> locationEntityList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LocationEntity.class), name2);
        return locationEntityList;
    }

    public List<LocationEntity> SearchLocationCount(String name) {
        String name2 = "%" + name + "%";
        name2 = name2.replace("%%", "%");
        String sql = "SELECT foo.name FROM(SELECT id,pac,name,lvl ,st_x(geom) as lng,st_y(geom) as lat " +
                "FROM b_gis_point_location_all WHERE name like ?) AS foo " +
                "LEFT JOIN b_gis_point_location_xiang as xiang ON xiang.pac=left(foo.pac, 9)||'000' " +
                "LEFT JOIN b_gis_point_location_xian as xian ON xian.pac=left(foo.pac, 6)||'000000' " +
                "LEFT JOIN b_gis_point_location_zhou as shi ON shi.pac=left(foo.pac, 4)||'00000000' " +
                "LEFT JOIN b_gis_point_location_sheng as sheng ON sheng.pac=left(foo.pac, 2)||'0000000000'";

        List<LocationEntity> locationEntityList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LocationEntity.class), name2);
        return locationEntityList;
    }

    public LocationResultEntity SearchLocationFull(String name, Integer pageSize, Integer pageNum) {
        String name2 = "%" + name + "%";
        name2 = name2.replace("%%", "%");
        String sql = "SELECT foo.id,foo.name,foo.pac,foo.lvl,foo.lng,foo.lat," +
                "xiang.name as name_xiang,xian.name as name_xian,shi.name as name_shi,sheng.name" +
                " as name_sheng FROM(SELECT id,pac,name,lvl ,st_x(geom) as lng,st_y(geom) as lat " +
                "FROM b_gis_point_location_all WHERE name like ?) AS foo " +
                "LEFT JOIN b_gis_point_location_xiang as xiang ON xiang.pac=left(foo.pac, 9)||'000' " +
                "LEFT JOIN b_gis_point_location_xian as xian ON xian.pac=left(foo.pac, 6)||'000000' " +
                "LEFT JOIN b_gis_point_location_zhou as shi ON shi.pac=left(foo.pac, 4)||'00000000' " +
                "LEFT JOIN b_gis_point_location_sheng as sheng ON sheng.pac=left(foo.pac, 2)||'0000000000' ORDER BY foo.pac " +
                " LIMIT " + pageSize + " OFFSET " + pageSize * (pageNum - 1);
        List<LocationEntity> locationEntityList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LocationEntity.class), name2);
        sql = "SELECT ceil(count(*)/"+pageSize+".0)  as total_page " +
                "FROM(SELECT id,pac,name,lvl ,st_x(geom) as lng,st_y(geom) as lat " +
                "FROM b_gis_point_location_all WHERE name like ?) AS foo " +
                "LEFT JOIN b_gis_point_location_xiang as xiang ON xiang.pac=left(foo.pac, 9)||'000' " +
                "LEFT JOIN b_gis_point_location_xian as xian ON xian.pac=left(foo.pac, 6)||'000000' " +
                "LEFT JOIN b_gis_point_location_zhou as shi ON shi.pac=left(foo.pac, 4)||'00000000' " +
                "LEFT JOIN b_gis_point_location_sheng as sheng ON sheng.pac=left(foo.pac, 2)||'0000000000'";
        Integer total_page = jdbcTemplate.queryForObject(sql,Integer.class, name2);
        LocationResultEntity locationResultEntity=new LocationResultEntity(locationEntityList,pageNum,total_page);
        return locationResultEntity;
    }




}
